The Sad TruthWhy Postgres Makes You Miserable

PGDay Lowlands
2024-09-13

Who am I

• Lætitia Avrot
• PostgreSQL Europe Treasurer
• #PostgresWomen Founder
• Practice Leader, Postgres & Security at EDB
• @l_avrot
• mydbanotebook.org / psql-tips.org

Agenda

• Things that are obvious
• Things that you don't understand
• Things that just happen
• Things that are complex
• Things that should be easier

Things that are obvious

Hard to write

• Postgrè
• Progrès (autocorrect?)
• Progress (autocorrect?)
• Postgre
• Postgres-QL
• Postgras
• Postgre-SQL
• PostgreSequel

Impossible to say

(Taken from the FAQ in Postgres wiki)

Let's have 3 names!!!

• PostgreSQL
• Postgres
• PG

The Logo

The Logo

“What do you think of the Postgres logo?”
fictional mother asked her fictionnal teenager daughter.

“The color is a little insipid but it's fine,”
the fictionnal daughter replied.

“Don't you think it feels a little old and outdated? ”
the fictionnal mother insisted.

“Well, it's not a community of youngsters either,”
the fictionnal daughter explained.

Things that you don't understand

Postgres is most of the time good with error messages

• It's still a stupid algorithm
• Extremely frustrating for humans
• Don't try it again without any change!

$ pg_dump --format=directory --file=test -verbose --jobs 5
  --dbname=mydb

pg_dump: last built-in OID is 16383 pg_dump: error:
   no matching extensions were found

$ export PGPASSWORD="******"

$ sudo -u postgres psql -d myDb -w --no-password -t
   -c "SELECT id FROM radusers WHERE id=1"

psql: fe_sendauth: no password supplied

create or replace function allRelevantTeas()
  returns table(tea_id integer) as

$allRelevantTeas$
  declare
    result refcursor;
    stmt text;
    countries_with_tea text[] := array[
      'england',
      'turkey',
      'india',
      'japan',
      'china'];

  begin
    stmt := '';

    for tea_drinker in countries_with_tea loop
      stmt := stmt ||
        format($$(select tea_id from %I)$$, tea_drinker);
      if tea_drinker <> 'china' then
        stmt := stmt || $$ union $$;
      end if;
    end loop;

    open result for execute stmt;
    return result;

  end
$allRelevantTeas$
language plpgsql stable;

select * from allRelevantTeas();

syntax error at or near "countries_with_tea"

create or replace function allRelevantTeas()
  returns table(tea_id integer) as

$allRelevantTeas$
  declare
    result refcursor;
    stmt text;
    countries_with_tea text[] := array[
      'england',
      'turkey',
      'india',
      'japan',
      'china'];

  begin
    stmt := '';

    for tea_drinker in countries_with_tea loop
      stmt := stmt ||
        format($$(select tea_id from %I)$$, tea_drinker);
      if tea_drinker <> 'china' then
        stmt := stmt || $$ union $$;
      end if;
    end loop;

    open result for execute stmt;
    return result;

  end
$allRelevantTeas$
language plpgsql stable;

 for tea_drinker in countries_with_tea loop 

 foreach tea_drinker in countries_with_tea loop 

Frustrating error messages

/* Use psql, the best Postgres client in the world */
\set ON_ERROR_STOP on

/* Making the script idempotent */
drop schema if exists aoc25 cascade;
create schema aoc25;
set search_path to 'aoc25';

/* Insert data into  table */
create table input (
  id integer generated always as identity primary key,
  data text not null
);

\copy input(data) from 'input.csv';

create function snafuToDec(snafu text) returns bigint as
$snafuToDec$
  with snafu(num, l) as (
    select string_to_array(snafu, null),
      char_length(snafu)
  )
  select sum(
      (case when element = '-' then '-1'
      when element = '=' then '-2'
      else element
      end)::bigint
      *
      power(5, l-idx)
    )
  from snafu, unnest(num) with ordinality as a(element, idx);
$snafuToDec$ immutable language sql;

alter table input add column decNumber bigint generated always as (
  snafuToDec(data)) stored;
create table power5 (num bigint, pow integer);
insert into power5 (num,pow) (
  select power(5,n), n
  from generate_series(1,26) t(n)
);

create function pow5(myNumber bigint) returns text as
$pow5$
  with recursive processignpow5(x, num, pow, div, remain, snafu) as (
    (
      /* Get the highest power of 5 */
      select myNumber as x,
        num,
        pow,
        myNumber/num as div,
        myNumber%num as remain,
        (myNumber/num)::text as snafu
      from power5
      where myNumber/num > 0
      order by num desc
      limit 1)
    union all
    (
      select x,
        power5.num,
        power5.pow,
        processignpow5.remain/power5.num as div,
        processignpow5.remain%power5.num as remain,
        processignpow5.snafu || (processignpow5.remain/power5.num)::text
      from processignpow5
        inner join power5
          on processignpow5.pow-1 = power5.pow)
  )
  /* To make the snafu conversion easier, let's add a 0 in the front */
  select '0' || snafu || remain::text
  from processignpow5
  order by num
  limit 1
  ;
$pow5$ language sql;

create function snafu(mydec5 text) returns text as 
$snafu$
  with recursive snafu(n, pow5, snafu) as (
    select 1 as n,
      /* If we have a number over 2 as a last digit, then we need to increase
       * the "new" last digit.
       * By increasing that number we might reach 5, which is bad, obvisouly as
       * this is a number on base 5.
       * So, we then need to increate the next digit and put 0 instead as a last
       * digit.
       * That way, we should never get 5 in the snafu digit */
      case when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) > 3
        /* Now we can safely take into account what happens should we have
         * a 5 digit as a last number in our array */
        then
          case
            when pow5[array_length(pow5,1)-1] = '4'
              then pow5[1:array_length(pow5,1)-3] || (pow5[array_length(pow5,1)-2]::int+1)::text || array['0']
            else pow5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
          end
      /* We know we will never get 5 as the digit last digit shouldthe array
       * length be 1 because we on purpose added a 0 in front of the base
       * 5 number we had converted. */  
      when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 2
        then array[pow5[1]] || (pow5[2]::int+1)::text
      when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 3
        then
          case
            when pow5[array_length(pow5,1)-1] = '4'
              then array[pow5[1]] || (pow5[2]::int+1)::text || array['0']
            else array[pow5[1]] || (pow5[2]::int+1)::text || pow5[3]
          end
      else pow5[1:array_length(pow5,1)-1]
      end as pow5,
      case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
        when pow5[array_length(pow5,1)]::int = 3 then '='
        when pow5[array_length(pow5,1)]::int = 4 then '-'
      end as snafu
    from (select string_to_array('013140400422344032342', null)) as finalpow5(pow5)
    union all
    select n+1,
      case when pow5[array_length(pow5,1)]::int > 2
        then pow5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
      else pow5[1:array_length(pow5,1)-1]
      end as pow5,
      (case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
        when pow5[array_length(pow5,1)]::int = 3 then '='
        when pow5[array_length(pow5,1)]::int = 4 then '-'
        /* Due to the treatment from before round, we can end up with 5, so we
         * need to take that into account */
        when pow5[array_length(pow5,1)]::int = 5 then '0'
      end) || snafu.snafu as snafu
    from snafu
    where array_length(pow5,1) > 0
  )
  select
    case when substring(snafu from 1 for 1) = '0'
      then substring(snafu from 2)
    else snafu
    end as firstStar
  from snafu
  where snafu is not null
  order by n desc
  limit 1
  ;
$snafu$ language sql;

select snafu(pow5(sum(decNumber)::bigint)) as firstStar
from input;

laetitia=# \i solution.sql

psql:solution.sql:142:
ERROR: column "por5" does not exist

LINE 43:
then por5[1:array_length(pow5,1)-2] || (pow5[array_l...
     ^
HINT: Perhaps you meant to reference the column
"snafu.pow5" or the column "*SELECT* 1.pow5"

/* Use psql, the best Postgres client in the world */
\set ON_ERROR_STOP on

/* Making the script idempotent */
drop schema if exists aoc25 cascade;
create schema aoc25;
set search_path to 'aoc25';

/* Insert data into  table */
create table input (
  id integer generated always as identity primary key,
  data text not null
);

\copy input(data) from 'input.csv';

create function snafuToDec(snafu text) returns bigint as
$snafuToDec$
  with snafu(num, l) as (
    select string_to_array(snafu, null),
      char_length(snafu)
  )
  select sum(
      (case when element = '-' then '-1'
      when element = '=' then '-2'
      else element
      end)::bigint
      *
      power(5, l-idx)
    )
  from snafu, unnest(num) with ordinality as a(element, idx);
$snafuToDec$ immutable language sql;

alter table input add column decNumber bigint generated always as (
  snafuToDec(data)) stored;
create table power5 (num bigint, pow integer);
insert into power5 (num,pow) (
  select power(5,n), n
  from generate_series(1,26) t(n)
);

create function pow5(myNumber bigint) returns text as
$pow5$
  with recursive processignpow5(x, num, pow, div, remain, snafu) as (
    (
      /* Get the highest power of 5 */
      select myNumber as x,
        num,
        pow,
        myNumber/num as div,
        myNumber%num as remain,
        (myNumber/num)::text as snafu
      from power5
      where myNumber/num > 0
      order by num desc
      limit 1)
    union all
    (
      select x,
        power5.num,
        power5.pow,
        processignpow5.remain/power5.num as div,
        processignpow5.remain%power5.num as remain,
        processignpow5.snafu || (processignpow5.remain/power5.num)::text
      from processignpow5
        inner join power5
          on processignpow5.pow-1 = power5.pow)
  )
  /* To make the snafu conversion easier, let's add a 0 in the front */
  select '0' || snafu || remain::text
  from processignpow5
  order by num
  limit 1
  ;
$pow5$ language sql;

create function snafu(mydec5 text) returns text as 
$snafu$
  with recursive snafu(n, pow5, snafu) as (
    select 1 as n,
      /* If we have a number over 2 as a last digit, then we need to increase
       * the "new" last digit.
       * By increasing that number we might reach 5, which is bad, obvisouly as
       * this is a number on base 5.
       * So, we then need to increate the next digit and put 0 instead as a last
       * digit.
       * That way, we should never get 5 in the snafu digit */
      case when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) > 3
        /* Now we can safely take into account what happens should we have
         * a 5 digit as a last number in our array */
        then
          case
            when pow5[array_length(pow5,1)-1] = '4'
              then por5[1:array_length(pow5,1)-3] || (pow5[array_length(pow5,1)-2]::int+1)::text || array['0']
            else pow5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
          end
      /* We know we will never get 5 as the digit last digit shouldthe array
       * length be 1 because we on purpose added a 0 in front of the base
       * 5 number we had converted. */  
      when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 2
        then array[pow5[1]] || (pow5[2]::int+1)::text
      when pow5[array_length(pow5,1)]::int > 2 and array_length(pow5,1) = 3
        then
          case
            when pow5[array_length(pow5,1)-1] = '4'
              then array[pow5[1]] || (pow5[2]::int+1)::text || array['0']
            else array[pow5[1]] || (pow5[2]::int+1)::text || pow5[3]
          end
      else pow5[1:array_length(pow5,1)-1]
      end as pow5,
      case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
        when pow5[array_length(pow5,1)]::int = 3 then '='
        when pow5[array_length(pow5,1)]::int = 4 then '-'
      end as snafu
    from (select string_to_array('013140400422344032342', null)) as finalpow5(pow5)
    union all
    select n+1,
      case when pow5[array_length(pow5,1)]::int > 2
        then por5[1:array_length(pow5,1)-2] || (pow5[array_length(pow5,1)-1]::int+1)::text
      else pow5[1:array_length(pow5,1)-1]
      end as pow5,
      (case when pow5[array_length(pow5,1)]::int < 3 then pow5[array_length(pow5,1)]
        when pow5[array_length(pow5,1)]::int = 3 then '='
        when pow5[array_length(pow5,1)]::int = 4 then '-'
        /* Due to the treatment from before round, we can end up with 5, so we
         * need to take that into account */
        when pow5[array_length(pow5,1)]::int = 5 then '0'
      end) || snafu.snafu as snafu
    from snafu
    where array_length(pow5,1) > 0
  )
  select
    case when substring(snafu from 1 for 1) = '0'
      then substring(snafu from 2)
    else snafu
    end as firstStar
  from snafu
  where snafu is not null
  order by n desc
  limit 1
  ;
$snafu$ language sql;

select snafu(pow5(sum(decNumber)::bigint)) as firstStar
from input;

Things that just happen

Locks

• ACID
• Make shorter transactions
• Use DML only when you want to write
• Lower down the isolation level?
• Go to NoSQL?

Dead Locks

• More locks → More probability
• Make shorter transactions
• Use DML only when you want to write
• Write data in the same order
• Lower down the isolation level?
• Go to NoSQL?

Replication lag

Replication lag

Replication lag

Replication lag

• Laws of Physics
• Only 1 thread applying WALs
• Are you using the right architecture?
• Scale up?

Things that are complex

Xid wraparound

• No infinite transaction number
• Can corrupt your whole cluster
• Dangerous!

Xid wraparound

• Was there an alternative at the time?
• ORA-01555 Snapshot Too Old
• ZHeap

Vacuum

• Keep older version of rows
• Until no one can read them (dead rows)
• Vacuum "removes" dead rows

Vacuum analyze

• Vacuum
• and analyze!

Vacuum freeze

• Vacuum
• and freeze!

Vacuum full 🤷‍♀️

And don't forget

• The major problen is stupid default values!
• But we don't know what are the best values!
• Tune it

Collations

• Depends on glibc
• Inside your OS
• Can corrupt your indexes or data
• Jérémie's talk this afternoon

Things that should be easier

Finding bloat?

-- new table bloat query
-- still needs work; is often off by +/- 20%
WITH constants AS (
    -- define some constants for sizes of things
    -- for reference down the query and easy maintenance
    SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma
),
no_stats AS (
    -- screen out table who have attributes
    -- which dont have stats, such as JSON
    SELECT table_schema, table_name, 
        n_live_tup::numeric as est_rows,
        pg_table_size(relid)::numeric as table_size
    FROM information_schema.columns
        JOIN pg_stat_user_tables as psut
           ON table_schema = psut.schemaname
           AND table_name = psut.relname
        LEFT OUTER JOIN pg_stats
        ON table_schema = pg_stats.schemaname
            AND table_name = pg_stats.tablename
            AND column_name = attname 
    WHERE attname IS NULL
        AND table_schema NOT IN ('pg_catalog', 'information_schema')
    GROUP BY table_schema, table_name, relid, n_live_tup
),
null_headers AS (
    -- calculate null header sizes
    -- omitting tables which dont have complete stats
    -- and attributes which aren't visible
    SELECT
        hdr+1+(sum(case when null_frac <> 0 THEN 1 else 0 END)/8) as nullhdr,
        SUM((1-null_frac)*avg_width) as datawidth,
        MAX(null_frac) as maxfracsum,
        schemaname,
        tablename,
        hdr, ma, bs
    FROM pg_stats CROSS JOIN constants
        LEFT OUTER JOIN no_stats
            ON schemaname = no_stats.table_schema
            AND tablename = no_stats.table_name
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
        AND no_stats.table_name IS NULL
        AND EXISTS ( SELECT 1
            FROM information_schema.columns
                WHERE schemaname = columns.table_schema
                    AND tablename = columns.table_name )
    GROUP BY schemaname, tablename, hdr, ma, bs
),
data_headers AS (
    -- estimate header and row size
    SELECT
        ma, bs, hdr, schemaname, tablename,
        (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
        (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM null_headers
),
table_estimates AS (
    -- make estimates of how large the table should be
    -- based on row and page size
    SELECT schemaname, tablename, bs,
        reltuples::numeric as est_rows, relpages * bs as table_bytes,
    CEIL((reltuples*
            (datahdr + nullhdr2 + 4 + ma -
                (CASE WHEN datahdr%ma=0
                    THEN ma ELSE datahdr%ma END)
                )/(bs-20))) * bs AS expected_bytes,
        reltoastrelid
    FROM data_headers
        JOIN pg_class ON tablename = relname
        JOIN pg_namespace ON relnamespace = pg_namespace.oid
            AND schemaname = nspname
    WHERE pg_class.relkind = 'r'
),
estimates_with_toast AS (
    -- add in estimated TOAST table sizes
    -- estimate based on 4 toast tuples per page because we dont have 
    -- anything better.  also append the no_data tables
    SELECT schemaname, tablename, 
        TRUE as can_estimate,
        est_rows,
        table_bytes + ( coalesce(toast.relpages, 0) * bs ) as table_bytes,
        expected_bytes + ( ceil( coalesce(toast.reltuples, 0) / 4 ) * bs ) as expected_bytes
    FROM table_estimates LEFT OUTER JOIN pg_class as toast
        ON table_estimates.reltoastrelid = toast.oid
            AND toast.relkind = 't'
),
table_estimates_plus AS (
-- add some extra metadata to the table data
-- and calculations to be reused
-- including whether we cant estimate it
-- or whether we think it might be compressed
    SELECT current_database() as databasename,
            schemaname, tablename, can_estimate, 
            est_rows,
            CASE WHEN table_bytes > 0
                THEN table_bytes::NUMERIC
                ELSE NULL::NUMERIC END
                AS table_bytes,
            CASE WHEN expected_bytes > 0 
                THEN expected_bytes::NUMERIC
                ELSE NULL::NUMERIC END
                    AS expected_bytes,
            CASE WHEN expected_bytes > 0 AND table_bytes > 0
                AND expected_bytes <= table_bytes
                THEN (table_bytes - expected_bytes)::NUMERIC
                ELSE 0::NUMERIC END AS bloat_bytes
    FROM estimates_with_toast
    UNION ALL
    SELECT current_database() as databasename, 
        table_schema, table_name, FALSE, 
        est_rows, table_size,
        NULL::NUMERIC, NULL::NUMERIC
    FROM no_stats
),
bloat_data AS (
    -- do final math calculations and formatting
    select current_database() as databasename,
        schemaname, tablename, can_estimate, 
        table_bytes, round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
        expected_bytes, round(expected_bytes/(1024^2)::NUMERIC,3) as expected_mb,
        round(bloat_bytes*100/table_bytes) as pct_bloat,
        round(bloat_bytes/(1024::NUMERIC^2),2) as mb_bloat,
        table_bytes, expected_bytes, est_rows
    FROM table_estimates_plus
)
-- filter output for bloated tables
SELECT databasename, schemaname, tablename,
    can_estimate,
    est_rows,
    pct_bloat, mb_bloat,
    table_mb
FROM bloat_data
-- this where clause defines which tables actually appear
-- in the bloat chart
-- example below filters for tables which are either 50%
-- bloated and more than 20mb in size, or more than 25%
-- bloated and more than 4GB in size
WHERE ( pct_bloat >= 50 AND mb_bloat >= 10 )
    OR ( pct_bloat >= 25 AND mb_bloat >= 1000 )
ORDER BY mb_bloat DESC;

Finding bloat?

-- btree index stats query
-- estimates bloat for btree indexes
WITH btree_index_atts AS (
    SELECT nspname, 
        indexclass.relname as index_name, 
        indexclass.reltuples, 
        indexclass.relpages, 
        indrelid, indexrelid,
        indexclass.relam,
        tableclass.relname as tablename,
        regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
        indexrelid as index_oid
    FROM pg_index
    JOIN pg_class AS indexclass ON pg_index.indexrelid = indexclass.oid
    JOIN pg_class AS tableclass ON pg_index.indrelid = tableclass.oid
    JOIN pg_namespace ON pg_namespace.oid = indexclass.relnamespace
    JOIN pg_am ON indexclass.relam = pg_am.oid
    WHERE pg_am.amname = 'btree' and indexclass.relpages > 0
         AND nspname NOT IN ('pg_catalog','information_schema')
    ),
index_item_sizes AS (
    SELECT
    ind_atts.nspname, ind_atts.index_name, 
    ind_atts.reltuples, ind_atts.relpages, ind_atts.relam,
    indrelid AS table_oid, index_oid,
    current_setting('block_size')::numeric AS bs,
    8 AS maxalign,
    24 AS pagehdr,
    CASE WHEN max(coalesce(pg_stats.null_frac,0)) = 0
        THEN 2
        ELSE 6
    END AS index_tuple_hdr,
    sum( (1-coalesce(pg_stats.null_frac, 0)) * coalesce(pg_stats.avg_width, 1024) ) AS nulldatawidth
    FROM pg_attribute
    JOIN btree_index_atts AS ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum
    JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
          -- stats for regular index columns
          AND ( (pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_catalog.pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE)) 
          -- stats for functional indexes
          OR   (pg_stats.tablename = ind_atts.index_name AND pg_stats.attname = pg_attribute.attname))
    WHERE pg_attribute.attnum > 0
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
),
index_aligned_est AS (
    SELECT maxalign, bs, nspname, index_name, reltuples,
        relpages, relam, table_oid, index_oid,
        coalesce (
            ceil (
                reltuples * ( 6 
                    + maxalign 
                    - CASE
                        WHEN index_tuple_hdr%maxalign = 0 THEN maxalign
                        ELSE index_tuple_hdr%maxalign
                      END
                    + nulldatawidth 
                    + maxalign 
                    - CASE /* Add padding to the data to align on MAXALIGN */
                        WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
                        ELSE nulldatawidth::integer%maxalign
                      END
                )::numeric 
              / ( bs - pagehdr::NUMERIC )
              +1 )
         , 0 )
      as expected
    FROM index_item_sizes
),
raw_bloat AS (
    SELECT current_database() as dbname, nspname, pg_class.relname AS table_name, index_name,
        bs*(index_aligned_est.relpages)::bigint AS totalbytes, expected,
        CASE
            WHEN index_aligned_est.relpages <= expected 
                THEN 0
                ELSE bs*(index_aligned_est.relpages-expected)::bigint 
            END AS wastedbytes,
        CASE
            WHEN index_aligned_est.relpages <= expected
                THEN 0 
                ELSE bs*(index_aligned_est.relpages-expected)::bigint * 100 / (bs*(index_aligned_est.relpages)::bigint) 
            END AS realbloat,
        pg_relation_size(index_aligned_est.table_oid) as table_bytes,
        stat.idx_scan as index_scans
    FROM index_aligned_est
    JOIN pg_class ON pg_class.oid=index_aligned_est.table_oid
    JOIN pg_stat_user_indexes AS stat ON index_aligned_est.index_oid = stat.indexrelid
),
format_bloat AS (
SELECT dbname as database_name, nspname as schema_name, table_name, index_name,
        round(realbloat) as bloat_pct, round(wastedbytes/(1024^2)::NUMERIC) as bloat_mb,
        round(totalbytes/(1024^2)::NUMERIC,3) as index_mb,
        round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
        index_scans
FROM raw_bloat
)
-- final query outputting the bloated indexes
-- change the where and order by to change
-- what shows up as bloated
SELECT *
FROM format_bloat
WHERE ( bloat_pct > 50 and bloat_mb > 10 )
ORDER BY bloat_pct DESC;

Finding all permissions?

WITH server_permissions AS (
        SELECT 
            r.rolname, 
            'Server_Permissions' AS "Level", 
            r.rolsuper, 
            r.rolinherit,
            r.rolcreaterole, 
            r.rolcreatedb, 
            r.rolcanlogin,
            ARRAY(
                SELECT b.rolname
                FROM pg_catalog.pg_auth_members m
                JOIN pg_catalog.pg_roles b ON m.roleid = b.oid
                WHERE m.member = r.oid
            ) AS memberof,
            r.rolbypassrls
        FROM pg_catalog.pg_roles r
        WHERE r.rolname !~ '^pg_'
    ),
    
    db_ownership AS (
        SELECT 
            r.rolname, 
            'DB_Ownership' AS "Level", 
            d.datname
        FROM pg_catalog.pg_database d, pg_catalog.pg_roles r
        WHERE d.datdba = r.oid
    ),
    
    schema_permissions AS (
        SELECT
            'Schema Permissions' AS "Level",                
            r.rolname AS role_name,
            nspname AS schema_name,
            pg_catalog.has_schema_privilege(r.rolname, nspname, 'CREATE') AS create_grant,
            pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE') AS usage_grant
        FROM pg_namespace pn, pg_catalog.pg_roles r
        WHERE array_to_string(nspacl, ',') LIKE '%' || r.rolname || '%' 
              AND nspowner > 1
    ),
    
    table_ownership AS (
        SELECT 
            'Table Ownership' AS "Level",
            tableowner, 
            schemaname, 
            tablename
        FROM pg_tables
        GROUP BY tableowner, schemaname, tablename
    ),
    
    object_permissions AS (
        SELECT  
            'Object Permissions' AS "Level",
            COALESCE(NULLIF(s[1], ''), 'public') AS rolname,
            n.nspname,
            relname, 
            CASE 
                WHEN relkind = 'm' THEN 'Materialized View'
                WHEN relkind = 'p' THEN 'Partitioned Table'
                WHEN relkind = 'S' THEN 'Sequence'
                WHEN relkind = 'I' THEN 'Partitioned Index'
                WHEN relkind = 'v' THEN 'View'
                WHEN relkind = 'i' THEN 'Index'
                WHEN relkind = 'c' THEN 'Composite Type'
                WHEN relkind = 't' THEN 'TOAST table'
                WHEN relkind = 'r' THEN 'Table'
                WHEN relkind = 'f' THEN 'Foreign Table'
            END AS "Object Type",
            s[2] AS privileges
        FROM 
            pg_class c
            JOIN pg_namespace n ON n.oid = relnamespace
            JOIN pg_roles r ON r.oid = relowner,
            UNNEST(COALESCE(relacl::text[], FORMAT('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl, 
            REGEXP_SPLIT_TO_ARRAY(acl, '=|/') s 
        WHERE relkind <> 'i' AND relkind <> 't'
    )   
    
    SELECT 
        "Level", 
        rolname AS "Role", 
        'N/A' AS "Object Name", 
        'N/A' AS "Schema Name", 
        'N/A' AS "DB Name", 
        'N/A' AS "Object Type", 
        'N/A' AS "Privileges", 
        rolsuper::text AS "Is SuperUser", 
        rolinherit::text,
        rolcreaterole::text, 
        rolcreatedb::text, 
        rolcanlogin::text,
        memberof::text,
        rolbypassrls::text 
    FROM server_permissions
    
    UNION
    
    SELECT 
        dow."Level", 
        dow.rolname,
        'N/A',  
        'N/A', 
        datname,
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM db_ownership AS dow 
    
    UNION
    
    SELECT
        "Level", 
        role_name, 
        'N/A', 
        schema_name, 
        'N/A', 
        'N/A',
        CASE 
            WHEN create_grant IS TRUE AND usage_grant IS TRUE THEN 'Usage+Create' 
            WHEN create_grant IS TRUE AND usage_grant IS FALSE THEN 'Create' 
            WHEN create_grant IS FALSE AND usage_grant IS TRUE THEN 'Usage' 
            ELSE 'None' 
        END, 
        'N/A', 
        'N/A', 
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM schema_permissions
    
    UNION
    
    SELECT 
        "Level", 
        tableowner, 
        tablename, 
        schemaname,
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM table_ownership
    
    UNION
    
    SELECT 
        "Level", 
        rolname, 
        relname,  
        nspname, 
        'N/A', 
        "Object Type", 
        privileges,
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM object_permissions
    ORDER BY "Role";

Major updates

• Flexibility
• Support for 5 major versions/years
• Do it!

Partitioning

• No creation of new partitions
• Especially for time series

In a nutshell

• PostgreSQL is good
• Sometimes the problem is you
• There are some pain points
• They are not easy to solve
• Come help solve them!!!

Question Time!